﻿CREATE PROCEDURE [dbo].[Text_BookTree_Cache_Refresh]
AS
BEGIN

	--DECLARE @urls TABLE
	--(
	--	[BookTreeId] BIGINT
	--	,[Url] NVARCHAR(MAX)
	--)
	BEGIN TRAN

	TRUNCATE TABLE [dbo].[text_BookTree_Cache]

	DECLARE @url NVARCHAR(MAX)
	DECLARE @bookTreeId BIGINT, @leftKey INT, @rightKey INT, @bookId UNIQUEIDENTIFIER

	DECLARE @CURSOR CURSOR
	DECLARE @NOTECURSOR CURSOR

	SET @CURSOR  = CURSOR SCROLL
	FOR
		SELECT [BookTreeId], [LeftKey], [RightKey], [BookId]
		FROM [dbo].[text_BookTree]
		WHERE [IsHead] = 1
	/*Открываем курсор*/
	OPEN @CURSOR
	/*Выбираем первую строку*/
	FETCH NEXT FROM @CURSOR INTO @bookTreeId, @leftKey, @rightKey, @bookId
	/*Выполняем в цикле перебор строк*/
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @url = ''

		SELECT @url = @url + '/' + [HashTag]
		FROM [dbo].[text_BookTree]
		WHERE [BookId] = @bookId AND [LeftKey] <= @leftKey AND [RightKey] >= @rightKey 
		ORDER BY [LeftKey] ASC

		INSERT INTO [dbo].[text_BookTree_Cache]([BookTreeId], [Url])
		VALUES (@bookTreeId, LOWER(@url))

	/*Выбираем следующую строку*/
	FETCH NEXT FROM @CURSOR INTO @bookTreeId, @leftKey, @rightKey, @bookId
	END
	CLOSE @CURSOR

	COMMIT TRAN

	--SELECT * 
	--FROM @urls
	--WHERE [Url] IS NOT NULL
END